#!bin/bash

if [ -n "$1" ] ; then
  data_date=$1
else
  data_date=`date -d '-1 day' +%F`
fi

ADS_PRODUCT_SEND_PAY_TABLE_SQL="
WITH time_ranges AS (
    SELECT '昨天'                    AS time_range,
           DATE_SUB('2025-07-30', 1) AS start_date,
           '2025-07-30'              AS end_date
    UNION ALL
    SELECT '7天'                     AS time_range,
           DATE_SUB('2025-07-30', 6) AS start_date,
           '2025-07-30'              AS end_date
    UNION ALL
    SELECT '30天'                     AS time_range,
           DATE_SUB('2025-07-30', 29) AS start_date,
           '2025-07-30'               AS end_date)
INSERT OVERWRITE TABLE jtp_ds_warehouse.ads_product_send_pay_table
SELECT tr.time_range,
       COUNT(DISTINCT IF(d.send_time >= tr.start_date AND d.send_time < tr.end_date, d.send_id, NULL)) AS send_count,
       COUNT(DISTINCT IF(d.verify_time >= tr.start_date AND d.verify_time < tr.end_date, d.verify_id, NULL)) AS pay_count
FROM jtp_ds_warehouse.dwd_customer_order_discount d
         CROSS JOIN time_ranges tr
WHERE d.dt BETWEEN DATE_SUB(tr.start_date, 1) AND tr.end_date
GROUP BY tr.time_range, tr.start_date, tr.end_date
ORDER BY CASE tr.time_range
             WHEN '昨天' THEN 1
             WHEN '7天' THEN 2
             WHEN '30天' THEN 3
             END;
"


ADS_SEND_DISCOUNT_TABLE_SQL="
WITH time_params AS (
    SELECT
        DATE_SUB('2025-07-30', 6) AS start_date,
        '2025-07-30' AS end_date
)
INSERT OVERWRITE TABLE jtp_ds_warehouse.ads_send_discount_table
SELECT
    d.send_id,
    d.send_time,
    d.customer_service_id,
    d.cs_name,
    d.activity_id,
    d.activity_name,
    d.product_id,
    d.sku_id,
    d.send_discount,
    d.valid_period,
    d.expire_time,
    d.is_used,
    d.verify_id,
    d.verify_time,
    d.order_id,
    d.order_pay_amount,
    d.actual_discount,
    IF(
                    d.send_time < tp.start_date
                AND d.verify_time >= tp.start_date
                AND d.verify_time < tp.end_date,
                    '是',
                    '否'
        ) AS is_cross_period_pay
FROM jtp_ds_warehouse.dwd_customer_order_discount d
         CROSS JOIN time_params tp
WHERE (
        (d.send_time >= tp.start_date AND d.send_time < tp.end_date) OR
        (d.verify_time >= tp.start_date AND d.verify_time < tp.end_date)
    )
  AND d.dt BETWEEN DATE_SUB(tp.start_date, 1) AND tp.end_date
ORDER BY d.send_time DESC;
"

ADS_CS_DISCOUNT_USAGE_RATE_TABLE_SQL="
WITH time_ranges AS (
        SELECT 'day'   AS time_type, DATE_SUB('2025-07-30', 1) AS start_date, '2025-07-30' AS end_date
    UNION ALL
    SELECT '7day'  AS time_type, DATE_SUB('2025-07-30', 6) AS start_date, '2025-07-30' AS end_date
    UNION ALL
    SELECT '30day' AS time_type, DATE_SUB('2025-07-30', 29) AS start_date, '2025-07-30' AS end_date
    )
INSERT OVERWRITE TABLE jtp_ds_warehouse.ads_cs_discount_usage_rate_table
SELECT
    tr.time_type,
    d.cs_id,
    d.cs_name,
    COUNT(DISTINCT IF(d.send_time BETWEEN tr.start_date AND tr.end_date, d.send_id, NULL)) AS send_count,
    RANK() OVER (
        PARTITION BY tr.time_type
        ORDER BY COUNT(DISTINCT IF(d.send_time BETWEEN tr.start_date AND tr.end_date, d.send_id, NULL)) DESC
        ) AS send_rank, --排名
    COUNT(DISTINCT IF(d.verify_time BETWEEN tr.start_date AND tr.end_date, d.verify_id, NULL)) AS verify_count,
    RANK() OVER (
        PARTITION BY tr.time_type
        ORDER BY COUNT(DISTINCT IF(d.verify_time BETWEEN tr.start_date AND tr.end_date, d.verify_id, NULL)) DESC
        ) AS verify_rank,
    IF(
                COUNT(DISTINCT IF(d.send_time BETWEEN tr.start_date AND tr.end_date, d.send_id, NULL)) = 0, 0,
                COUNT(DISTINCT IF(d.verify_time BETWEEN tr.start_date AND tr.end_date, d.verify_id, NULL)) / COUNT(DISTINCT IF(d.send_time BETWEEN tr.start_date AND tr.end_date, d.send_id, NULL))
        ) AS discount_usage_rate,
    SUM(IF(d.send_time BETWEEN tr.start_date AND tr.end_date, d.send_discount, 0)) AS total_discount_amount,
    IF(
                COUNT(DISTINCT IF(d.send_time BETWEEN tr.start_date AND tr.end_date, d.send_id, NULL)) = 0,0,
                COUNT(DISTINCT IF(d.verify_time BETWEEN tr.start_date AND tr.end_date, d.verify_id, NULL)) /COUNT(DISTINCT IF(d.send_time BETWEEN tr.start_date AND tr.end_date, d.send_id, NULL))
        ) AS inquiry_order_rate,
    IF(
                COUNT(DISTINCT IF(d.send_time >= tr.start_date AND d.send_time < tr.end_date, d.send_id, NULL)) = 0,
                0,
                COUNT(DISTINCT IF(d.verify_time >= tr.start_date AND d.verify_time < tr.end_date, d.verify_id, NULL))
                    /
                COUNT(DISTINCT IF(d.send_time >= tr.start_date AND d.send_time < tr.end_date, d.send_id, NULL))
        ) AS verification_rate
FROM jtp_ds_warehouse.dwd_customer_order_discount d
         CROSS JOIN time_ranges tr
WHERE
    d.dt BETWEEN DATE_SUB(tr.start_date, 1) AND tr.end_date
  AND d.cs_id IS NOT NULL
GROUP BY tr.time_type, d.cs_id, d.cs_name
ORDER BY tr.time_type, discount_usage_rate DESC;
"


ADS_PRODUCT_YH_SQL="
WITH valid_discounts AS (
        SELECT
        d.*,
        IF(d.sku_id IS NOT NULL, apd.sku_price, apd.original_price) AS product_price,
    DATE_FORMAT(d.send_time, 'yyyy-MM-dd') AS send_date
    FROM jtp_ds_warehouse.dwd_customer_order_discount d
    LEFT JOIN jtp_ds_warehouse.dim_activity_product_detail apd
    ON d.activity_id = apd.activity_id
    AND d.product_id = apd.product_id
    AND d.sku_id = apd.sku_id
    AND d.dt = apd.dt
    WHERE
(d.send_discount <= IF(d.sku_id IS NOT NULL, apd.sku_price, apd.original_price) OR apd.original_price IS NULL)
    AND d.send_id IS NOT NULL
    AND d.send_time BETWEEN DATE_SUB('2025-07-30', 29) AND '2025-07-30'
    ),
    dedup_discounts AS (
                           SELECT
                           *
                           FROM (
                           SELECT
                           *,
                           ROW_NUMBER() OVER (
                                                 PARTITION BY product_id, sku_id, user_id, send_date
                                                 ORDER BY send_time ASC
                                             ) AS send_rn
    FROM valid_discounts
    ) t
    WHERE send_rn = 1
    )
INSERT OVERWRITE TABLE jtp_ds_warehouse.ads_product_yh
SELECT
    d.product_id,  -- 明确指定来自dedup_discounts表
    d.sku_id,      -- 明确指定来自dedup_discounts表
    apd.product_name,
    apd.sku_name,
    IF(d.sku_id IS NOT NULL, apd.sku_price, apd.original_price) AS product_original_price,
    COUNT(DISTINCT send_id) AS discount_send_count,
    COUNT(DISTINCT IF(d.is_used = '1', d.verify_id, NULL)) AS discount_used_count,
    IF(
                COUNT(DISTINCT d.send_id) = 0,
                0,
                COUNT(DISTINCT IF(d.is_used = '1', d.verify_id, NULL)) /
                COUNT(DISTINCT d.send_id)
        ) AS discount_usage_rate,
    SUM(d.send_discount) AS total_discount_amount,
    SUM(IF(d.is_used = '1', d.order_pay_amount, 0)) AS total_actual_pay_amount
FROM dedup_discounts d
         LEFT JOIN jtp_ds_warehouse.dim_activity_product_detail apd
                   ON d.activity_id = apd.activity_id
                       AND d.product_id = apd.product_id
                       AND d.sku_id = apd.sku_id
                       AND d.dt = apd.dt
WHERE d.sku_id IS NOT NULL
GROUP BY
    d.product_id,
    d.sku_id,
    apd.product_name,
    apd.sku_name,
    IF(d.sku_id IS NOT NULL, apd.sku_price, apd.original_price)
ORDER BY discount_used_count DESC;
"

ADS_PRODUCT_USED_RATE_SQL="
WITH click_data AS (
    SELECT
        d.send_id,
        d.product_id,
        d.sku_id,
        d.user_id,
        COUNT(DISTINCT d.order_id) AS click_count
    FROM jtp_ds_warehouse.dwd_customer_order_discount d
    WHERE d.send_time IS NOT NULL
      AND d.dt BETWEEN DATE_SUB('2025-07-30', 29) AND '2025-07-30'
    GROUP BY d.send_id, d.product_id, d.sku_id, d.user_id
),
     usage_data AS (
         SELECT
             send_id,
             product_id,
             sku_id,
             user_id,
             COUNT(DISTINCT verify_id) AS usage_count
         FROM jtp_ds_warehouse.dwd_customer_order_discount
         WHERE is_used = '1'
           AND dt BETWEEN DATE_SUB('2025-07-30', 29) AND '2025-07-30'
         GROUP BY send_id, product_id, sku_id, user_id
     )
INSERT OVERWRITE TABLE jtp_ds_warehouse.ads_product_used_rate
SELECT
    c.product_id,
    apd.product_name,
    c.sku_id,
    apd.sku_name,
    SUM(c.click_count) AS total_click_count,
    SUM(u.usage_count) AS total_usage_count,
    -- 优惠使用率 = 使用次数 / 点击次数
    IF(SUM(c.click_count) = 0, 0, SUM(u.usage_count) / SUM(c.click_count)) AS discount_usage_rate
FROM click_data c
         LEFT JOIN usage_data u
                   ON c.send_id = u.send_id
                       AND c.product_id = u.product_id
                       AND c.sku_id = u.sku_id
                       AND c.user_id = u.user_id
         LEFT JOIN jtp_ds_warehouse.dim_activity_product_detail apd
                   ON c.product_id = apd.product_id
                       AND c.sku_id = apd.sku_id
                       AND apd.dt = '2025-07-30'
WHERE product_name IS NOT NULL
GROUP BY c.product_id, apd.product_name, c.sku_id, apd.sku_name
ORDER BY discount_usage_rate DESC;
"

ADS_KEHU_USED_RATE_SQL="
WITH customer_usage AS (
    SELECT
        user_id,
        COUNT(DISTINCT verify_id) AS usage_count
    FROM jtp_ds_warehouse.dwd_customer_order_discount
    WHERE is_used = '1'
      AND dt BETWEEN DATE_SUB('2025-07-30', 29) AND '2025-07-30'
    GROUP BY user_id
)
INSERT OVERWRITE TABLE jtp_ds_warehouse.ads_kehu_used_rate
SELECT
    COUNT(DISTINCT user_id) AS total_usage_customers,
    AVG(usage_count) AS avg_usage_count,
    round(AVG(usage_count) / COUNT(DISTINCT user_id) * 100,4) AS customer_participation_rate
FROM customer_usage;
"

ADS_NEW_OLD_USE_ZB_SQL="
WITH user_first_order AS (
    SELECT
        user_id,
        MIN(order_time) AS first_order_time
    FROM jtp_ds_warehouse.dwd_customer_order_discount
    WHERE order_time IS NOT NULL
    GROUP BY user_id
),
     discount_users AS (
         SELECT
             d.user_id,
             d.verify_time,
             d.verify_id,
             u.first_order_time,
             IF(u.first_order_time BETWEEN DATE_SUB('2025-07-30', 29) AND '2025-07-30',
                'new_customer', 'old_customer') AS user_type
         FROM jtp_ds_warehouse.dwd_customer_order_discount d
                  JOIN user_first_order u ON d.user_id = u.user_id
         WHERE d.is_used = '1'
           AND d.dt BETWEEN DATE_SUB('2025-07-30', 29) AND '2025-07-30'
     ),
     old_user_reuse AS (
         SELECT
             user_id,
             COUNT(DISTINCT verify_id) AS usage_count,
             IF(COUNT(DISTINCT verify_id) >= 2, 1, 0) AS is_reused
         FROM discount_users
         WHERE user_type = 'old_customer'
         GROUP BY user_id
     )
INSERT OVERWRITE TABLE jtp_ds_warehouse.ads_new_old_use_zb
SELECT
    SUM(IF(user_type = 'new_customer', 1, 0)) / COUNT(DISTINCT user_id) AS new_customer_ratio,
    SUM(IF(user_type = 'old_customer', 1, 0)) / COUNT(DISTINCT user_id) AS old_customer_ratio,
    ( SUM(IF(user_type = 'old_customer', 1, 0)) / COUNT(DISTINCT user_id) /
      SUM(IF(user_type = 'new_customer', 1, 0)) / COUNT(DISTINCT user_id)
    ) AS old_user_reuse_rate
FROM discount_users;
"

/opt/module/spark/bin/beeline -u jdbc:hive2://node101:10001 -n bwie -p 123456 -e "${ADS_PRODUCT_SEND_PAY_TABLE_SQL}${ADS_SEND_DISCOUNT_TABLE_SQL}${ADS_CS_DISCOUNT_USAGE_RATE_TABLE_SQL}${ADS_PRODUCT_YH_SQL}${ADS_PRODUCT_USED_RATE_SQL}${ADS_KEHU_USED_RATE_SQL}${ADS_NEW_OLD_USE_ZB_SQL}
"
